﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using XiaoGang.Soft.XGWMS.COM;

namespace XiaoGang.Soft.XGWMS.DAL_SQL
{
    public class DAL_M008_STOREROOM
    {
        /// <summary>
        /// <p><b>方法名称</b>Init_Combox</p>	
        /// <p><b>概要説明  :</b>ComBox控件的初始化</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue Init_Combox()
        {
            return XGWMS.DBUtility.SqlHelper.Query(DAL_M008_STOREROOM_SQL.Init_Combox_SQL);
        }

        /// <summary>
        /// <p><b>方法名称</b>GetSingleData</p>	
        /// <p><b>概要説明  :</b>取得单条仓库信息</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">检索条件</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue GetSingleData(string StoreRoomID)
        {
            SqlParameter parameter = new SqlParameter("@STOREROOM_ID", SqlDbType.Char, 12);
            parameter.Value = StoreRoomID;
            return XGWMS.DBUtility.SqlHelper.Query(DAL_M008_STOREROOM_SQL.Get_Single_Data_SQL, parameter);
        }

        /// <summary>
        /// <p><b>方法名称</b>CheckName</p>	
        /// <p><b>概要説明  :</b>取得单条仓库信息</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">检索条件</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue CheckName(string StoreName)
        {
            return CheckStoreRoomNameWithoutID("", StoreName);
        }

        /// <summary>
        /// <p><b>方法名称</b>StorageRoomDeleteCheck</p>	
        /// <p><b>概要説明  :</b>检测仓库有没有被使用</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/09/17</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="string" name="strStorageRoomID">仓库编码</param>
        /// <returns type = "ReturnValue"></returns>
        public ReturnValue StoreRoomDeleteCheck(string strStorageRoomID)
        {
            SqlParameter parameter = new SqlParameter("@STOREROOM_ID", SqlDbType.Char, 12);
            parameter.Value = strStorageRoomID.Trim();
            return XGWMS.DBUtility.SqlHelper.Query(DAL_M008_STOREROOM_SQL.StorageRoom_Delete_Check, parameter);
        }

        /// <summary>
        /// <p><b>方法名称</b>SearchProcess</p>	
        /// <p><b>概要説明  :</b>仓库信息的检索</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">检索条件</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue SearchProcess(MODEL.MOD_M008_STOREROOM model)
        {
            StringBuilder strSql = new StringBuilder();            

            strSql.AppendFormat(" SELECT    M008_STOREROOM.[CREATE_DATETIME] {0}", (char)13);
            strSql.AppendFormat("          ,M008_STOREROOM.[UPDATE_DATETIME] {0}", (char)13);
            strSql.AppendFormat("          ,M008_STOREROOM.[TRANS_STATE] {0}", (char)13);
            strSql.AppendFormat("          ,M008_STOREROOM.[UPDATER_ID] {0}", (char)13);
            strSql.AppendFormat("          ,M008_STOREROOM.[TERMINAL_CD] {0}", (char)13);
            strSql.AppendFormat("          ,M008_STOREROOM.[STOREROOM_ID] {0}", (char)13);
            strSql.AppendFormat("          ,M008_STOREROOM.[STOREROOM_NM] {0}", (char)13);
            strSql.AppendFormat("          ,M008_STOREROOM.[STOREROOM_ADDRESS] {0}", (char)13);
            strSql.AppendFormat("          ,M008_STOREROOM.[STOREROOM_PHONE] {0}", (char)13);
            strSql.AppendFormat("          ,M008_STOREROOM.[STOREROOM_MANAGER]   {0}", (char)13);
            strSql.AppendFormat("          ,M003_USER.[USER_NAME] AS STOREROOM_MANAGER_NM {0}", (char)13);            
            strSql.AppendFormat("          ,CASE M008_STOREROOM.[STOREROOM_STATE] {0}", (char)13);
            strSql.AppendFormat("                WHEN '0' THEN '可用'   {0}", (char)13);
            strSql.AppendFormat("                WHEN '1' THEN '不可用' {0}", (char)13);
            strSql.AppendFormat("           END AS STOREROOM_STATE      {0}", (char)13);  
            strSql.AppendFormat("          ,M008_STOREROOM.[MEMO] {0}", (char)13);
            strSql.AppendFormat(" FROM      [M008_STOREROOM]   {0}", (char)13);
            strSql.AppendFormat(" LEFT JOIN [M003_USER] {0}", (char)13);
            strSql.AppendFormat(" ON        STOREROOM_MANAGER = USER_ID   {0}", (char)13);
            strSql.AppendFormat(" WHERE     1 = 1             {0}", (char)13);
            strSql.AppendFormat(" AND       M008_STOREROOM.TRANS_STATE <> '3'  {0}", (char)13);
            if (0 != model.STOREROOM_ID.Trim().Length)
            {
                strSql.AppendFormat(" AND   M008_STOREROOM.STOREROOM_ID LIKE '%{0}%'  {1}", model.STOREROOM_ID, (char)13);
            }
            if (0 != model.STOREROOM_MANAGER.Trim().Length)
            {
                strSql.AppendFormat(" AND   M008_STOREROOM.STOREROOM_MANAGER = '{0}'  {1}", model.STOREROOM_MANAGER, (char)13);
            }
            if (0 != model.STOREROOM_STATE.Trim().Length && model.STOREROOM_STATE.Trim() != "2")
            {
                strSql.AppendFormat(" AND   M008_STOREROOM.STOREROOM_STATE = '{0}'  {1}", model.STOREROOM_STATE, (char)13);
            }
            if (0 != model.STOREROOM_ADDRESS.Trim().Length)
            {
                strSql.AppendFormat(" AND   M008_STOREROOM.STOREROOM_ADDRESS LIKE '%{0}%'  {1}", model.STOREROOM_ADDRESS, (char)13);
            }
            if (0 != model.STOREROOM_NM.Trim().Length)
            {
                strSql.AppendFormat(" AND   M008_STOREROOM.STOREROOM_NM LIKE '%{0}%'  {1}", model.STOREROOM_NM, (char)13);
            }
            if (0 != model.STOREROOM_PHONE.Trim().Length)
            {
                strSql.AppendFormat(" AND   M008_STOREROOM.STOREROOM_PHONE LIKE '%{0}%'  {1}", model.STOREROOM_PHONE, (char)13);
            }
            if (0 != model.MEMO.Trim().Length)
            {
                strSql.AppendFormat(" AND   M008_STOREROOM.MEMO LIKE '%{0}%'  {1}", model.MEMO,  (char)13);
            }
            strSql.AppendFormat(" ORDER BY  M008_STOREROOM.STOREROOM_ID       {0}",  (char)13);

            return XGWMS.DBUtility.SqlHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// <p><b>方法名称</b>InsertProcess</p>	
        /// <p><b>概要説明  :</b>仓库信息的插入</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">插入条件</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue InsertProcess(MODEL.MOD_M008_STOREROOM model)
        {
            SqlParameter[] parameters = {			
					new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
					new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
					new SqlParameter("@STOREROOM_ID", SqlDbType.Char,12),
					new SqlParameter("@STOREROOM_NM", SqlDbType.VarChar,256),
					new SqlParameter("@STOREROOM_ADDRESS", SqlDbType.VarChar,256),
					new SqlParameter("@STOREROOM_PHONE", SqlDbType.Char,20),
					new SqlParameter("@STOREROOM_MANAGER", SqlDbType.Char,12),
					new SqlParameter("@STOREROOM_STATE", SqlDbType.Char,1),
					new SqlParameter("@MEMO", SqlDbType.VarChar,256)};
            parameters[0].Value = model.UPDATER_ID;
            parameters[1].Value = model.TERMINAL_CD;
            parameters[2].Value = model.STOREROOM_ID;
            parameters[3].Value = model.STOREROOM_NM;
            parameters[4].Value = model.STOREROOM_ADDRESS;
            parameters[5].Value = model.STOREROOM_PHONE;
            parameters[6].Value = model.STOREROOM_MANAGER;
            parameters[7].Value = model.STOREROOM_STATE;
            parameters[8].Value = model.MEMO;

            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M008_STOREROOM_SQL.Insert_STOREROOM_SQL, parameters);
        }

        /// <summary>
        /// <p><b>方法名称</b>UpdateProcess</p>	
        /// <p><b>概要説明  :</b>仓库信息的更新</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">更新信息和条件</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue UpdateProcess(MODEL.MOD_M008_STOREROOM model)
        {
            SqlParameter[] parameters = {					
					new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
					new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
					new SqlParameter("@STOREROOM_ID", SqlDbType.Char,12),
					new SqlParameter("@STOREROOM_NM", SqlDbType.VarChar,256),
					new SqlParameter("@STOREROOM_ADDRESS", SqlDbType.VarChar,256),
					new SqlParameter("@STOREROOM_PHONE", SqlDbType.Char,20),
					new SqlParameter("@STOREROOM_MANAGER", SqlDbType.Char,12),
					new SqlParameter("@STOREROOM_STATE", SqlDbType.Char,1),
					new SqlParameter("@MEMO", SqlDbType.VarChar,256)};
            parameters[0].Value = model.UPDATER_ID;
            parameters[1].Value = model.TERMINAL_CD;
            parameters[2].Value = model.STOREROOM_ID;
            parameters[3].Value = model.STOREROOM_NM;
            parameters[4].Value = model.STOREROOM_ADDRESS;
            parameters[5].Value = model.STOREROOM_PHONE;
            parameters[6].Value = model.STOREROOM_MANAGER;
            parameters[7].Value = model.STOREROOM_STATE;
            parameters[8].Value = model.MEMO;
            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M008_STOREROOM_SQL.Update_STOREROOM_SQL, parameters);
        }

        /// <summary>
        /// <p><b>方法名称</b>LogicDeleteProcess</p>	
        /// <p><b>概要説明  :</b>仓库信息的逻辑删除</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">更新信息和条件</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue LogicDeleteProcess(MODEL.MOD_M008_STOREROOM model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@TRANS_STATE", SqlDbType.Char,1),
					new SqlParameter("@UPDATER_ID", SqlDbType.VarChar,12),
					new SqlParameter("@TERMINAL_CD", SqlDbType.VarChar,256),
                    new SqlParameter("@STOREROOM_ID", SqlDbType.Char,12)};
            parameters[0].Value = model.TRANS_STATE;
            parameters[1].Value = model.UPDATER_ID;
            parameters[2].Value = model.TERMINAL_CD;
            parameters[3].Value = model.STOREROOM_ID;
            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M008_STOREROOM_SQL.Logic_Delete_STOREROOM_SQL, parameters);
        }

        /// <summary>
        /// <p><b>方法名称</b>PhysicalDeleteProcess</p>	
        /// <p><b>概要説明  :</b>仓库信息的物理删除</p>
        /// <p><b>作成者    :</b>万岗</p>	
        /// <p><b>作成日    :</b>2008/08/28</p>	
        /// <p><b>修改历史  :</b></p>	
        /// </summary>	
        /// <param type="class" name="model">删除条件</param>
        /// <returns type = "ReturnValue">执行结果信息</returns>
        public ReturnValue PhysicalDeleteProcess(string StoreRoomID)
        {
            SqlParameter parameter =
               new SqlParameter("@STOREROOM_ID", SqlDbType.VarChar, 12);
            parameter.Value = StoreRoomID;
            return XGWMS.DBUtility.SqlHelper.ExecuteSql(DAL_M008_STOREROOM_SQL.Physical_Delete_STOREROOM_SQL, parameter);
        }

        /// <summary>
        /// 获取除本ID的同名仓库名称
        /// </summary>
        /// <param name="StoreRoomID"></param>
        /// <param name="StoreRoomName"></param>
        /// <returns></returns>
        public ReturnValue CheckStoreRoomNameWithoutID(string StoreRoomID, string StoreRoomName)
        {
            string strWhere = string.Empty;
            if (StoreRoomID.Length > 0)
            {
                strWhere += String.Format(" and STOREROOM_ID <> '{0}'", StoreRoomID);
            }
            if (StoreRoomName.Length > 0)
            {
                strWhere += String.Format(" and STOREROOM_NM = '{0}'", StoreRoomName);
            }
            return XGWMS.DBUtility.SqlHelper.Query(DAL_M008_STOREROOM_SQL.Check_Name_SQL + strWhere);
        }
    }
}
